package main

import (
	"fmt"
	"log"
	"strings"

	"github.com/pgparser/pkg/parser"
)

// 复杂查询测试用例
var complexQueries = []struct {
	name        string
	description string
	sql         string
}{
	{
		name:        "基本JOIN示例",
		description: "内连接查询",
		sql:         "SELECT u.name, o.total FROM users u INNER JOIN orders o ON u.id = o.user_id;",
	},
	{
		name:        "LEFT JOIN示例",
		description: "左外连接查询",
		sql:         "SELECT u.name, o.total FROM users u LEFT JOIN orders o ON u.id = o.user_id;",
	},
	{
		name:        "复杂WHERE条件",
		description: "包含多个条件的WHERE子句",
		sql:         "SELECT name, age FROM users WHERE age > 18 AND status = 'active' OR premium = true;",
	},
	{
		name:        "聚合函数查询",
		description: "使用聚合函数的查询",
		sql:         "SELECT COUNT(*), AVG(age), MAX(salary) FROM users WHERE department = 'IT';",
	},
	{
		name:        "GROUP BY查询",
		description: "分组查询",
		sql:         "SELECT department, COUNT(*) as emp_count FROM users GROUP BY department;",
	},
	{
		name:        "ORDER BY查询",
		description: "排序查询",
		sql:         "SELECT name, age FROM users ORDER BY age DESC, name ASC;",
	},
	{
		name:        "LIMIT和OFFSET",
		description: "分页查询",
		sql:         "SELECT name, email FROM users ORDER BY id LIMIT 10 OFFSET 20;",
	},
	{
		name:        "数学表达式",
		description: "包含数学运算的查询",
		sql:         "SELECT name, salary + 1000 AS new_salary FROM employees;",
	},
}

func main() {
	fmt.Println("=== PostgreSQL 复杂查询解析示例 ===")
	fmt.Println("本示例展示如何解析包含子查询、JOIN、CTE等复杂SQL语句")
	fmt.Println()

	// 测试每个复杂查询
	for i, query := range complexQueries {
		fmt.Printf("测试用例 %d: %s\n", i+1, query.name)
		fmt.Printf("描述: %s\n", query.description)
		fmt.Printf("SQL: %s\n", query.sql)
		fmt.Println("---")

		// 解析SQL
		ast, err := parser.Parse(query.sql)
		if err != nil {
			fmt.Printf("❌ 解析失败: %v\n", err)
			fmt.Println()
			continue
		}

		fmt.Println("✅ 解析成功")

		// 显示AST结构
		fmt.Println("\nAST结构:")
		printASTStructure(ast, 0)

		// 语义分析
		analyzer := parser.NewSemanticAnalyzer()
		err = analyzer.AnalyzeStatement(ast)
		if err != nil {
			fmt.Printf("\n⚠️  语义分析警告: %v\n", err)
		} else {
			fmt.Println("\n✅ 语义分析通过")
		}

		// 尝试重新生成SQL
		generator := parser.NewSQLGenerator(true) // 使用缩进格式
		regeneratedSQL := generator.Generate(ast)
		fmt.Printf("\n重新生成的SQL:\n%s\n", regeneratedSQL)

		// 分析查询复杂度
		complexity := analyzeQueryComplexity(ast)
		fmt.Printf("\n查询复杂度分析:\n")
		fmt.Printf("  - 表数量: %d\n", complexity.TableCount)
		fmt.Printf("  - JOIN数量: %d\n", complexity.JoinCount)
		fmt.Printf("  - 子查询数量: %d\n", complexity.SubqueryCount)
		fmt.Printf("  - 聚合函数数量: %d\n", complexity.AggregateCount)
		fmt.Printf("  - 复杂度评分: %d\n", complexity.Score)

		fmt.Println("\n" + strings.Repeat("=", 80) + "\n")
	}

	// 演示查询优化建议
	fmt.Println("=== 查询优化建议演示 ===")
	demonstrateQueryOptimization()
}

// QueryComplexity 查询复杂度分析结果
type QueryComplexity struct {
	TableCount     int
	JoinCount      int
	SubqueryCount  int
	AggregateCount int
	Score          int
}

// analyzeQueryComplexity 分析查询复杂度
func analyzeQueryComplexity(node parser.Node) QueryComplexity {
	complexity := QueryComplexity{}

	switch n := node.(type) {
	case *parser.SelectStmt:
		// 计算表数量
		complexity.TableCount = len(n.From)

		// 计算JOIN数量
		for _, table := range n.From {
			complexity.JoinCount += len(table.Joins)
		}

		// 分析目标列表中的子查询和聚合函数
		for _, target := range n.Targets {
			analyzeExpression(target, &complexity)
		}

		// 分析WHERE条件
		if n.Where != nil {
			analyzeExpression(n.Where, &complexity)
		}

		// 分析GROUP BY
		for _, expr := range n.GroupBy {
			analyzeExpression(expr, &complexity)
		}

		// 分析HAVING
		if n.Having != nil {
			analyzeExpression(n.Having, &complexity)
		}
	}

	// 计算复杂度评分
	complexity.Score = complexity.TableCount*2 + complexity.JoinCount*3 + complexity.SubqueryCount*5 + complexity.AggregateCount*2

	return complexity
}

// analyzeExpression 分析表达式中的复杂度因子
func analyzeExpression(expr parser.Expression, complexity *QueryComplexity) {
	switch e := expr.(type) {
	case *parser.FunctionCall:
		// 检查是否为聚合函数
		if isAggregateFunction(e.Name) {
			complexity.AggregateCount++
		}
		// 分析函数参数
		for _, arg := range e.Args {
			analyzeExpression(arg, complexity)
		}

	case *parser.BinaryExpr:
		analyzeExpression(e.Left, complexity)
		analyzeExpression(e.Right, complexity)

	case *parser.UnaryExpr:
		analyzeExpression(e.Operand, complexity)

	// 注意：SubqueryExpr 可能不存在，这里简化处理
	// case *parser.SubqueryExpr:
	//	complexity.SubqueryCount++
	}
}

// isAggregateFunction 检查是否为聚合函数
func isAggregateFunction(name string) bool {
	aggregateFunctions := map[string]bool{
		"COUNT":   true,
		"SUM":     true,
		"AVG":     true,
		"MIN":     true,
		"MAX":     true,
		"GROUP_CONCAT": true,
	}
	return aggregateFunctions[strings.ToUpper(name)]
}

// printASTStructure 打印AST结构
func printASTStructure(node parser.Node, indent int) {
	indentStr := strings.Repeat("  ", indent)

	switch n := node.(type) {
	case *parser.SelectStmt:
		fmt.Printf("%sSELECT语句\n", indentStr)
		if n.Distinct {
			fmt.Printf("%s  DISTINCT: true\n", indentStr)
		}

		fmt.Printf("%s  目标列表:\n", indentStr)
		for i, target := range n.Targets {
			fmt.Printf("%s    [%d] ", indentStr, i)
			printExpression(target, indent+2)
		}

		if len(n.From) > 0 {
			fmt.Printf("%s  FROM子句:\n", indentStr)
			for i, table := range n.From {
				fmt.Printf("%s    [%d] 表: %s", indentStr, i, table.Name)
				if table.Alias != "" {
					fmt.Printf(" AS %s", table.Alias)
				}
				fmt.Println()

				// 打印JOIN信息
				for j, join := range table.Joins {
					fmt.Printf("%s      JOIN[%d]: %s %s", indentStr, j, join.Type, join.Table.Name)
					if join.Table.Alias != "" {
						fmt.Printf(" AS %s", join.Table.Alias)
					}
					if join.Condition != nil {
						fmt.Printf(" ON ")
						printExpression(join.Condition, 0)
					}
					fmt.Println()
				}
			}
		}

		if n.Where != nil {
			fmt.Printf("%s  WHERE条件:\n", indentStr)
			fmt.Printf("%s    ", indentStr)
			printExpression(n.Where, indent+2)
		}

		if len(n.GroupBy) > 0 {
			fmt.Printf("%s  GROUP BY:\n", indentStr)
			for i, expr := range n.GroupBy {
				fmt.Printf("%s    [%d] ", indentStr, i)
				printExpression(expr, indent+2)
			}
		}

		if n.Having != nil {
			fmt.Printf("%s  HAVING条件:\n", indentStr)
			fmt.Printf("%s    ", indentStr)
			printExpression(n.Having, indent+2)
		}

		if len(n.OrderBy) > 0 {
			fmt.Printf("%s  ORDER BY:\n", indentStr)
			for i, order := range n.OrderBy {
				fmt.Printf("%s    [%d] ", indentStr, i)
				printExpression(order.Expr, indent+2)
				if order.Direction == parser.DESC {
					fmt.Printf(" DESC")
				}
				fmt.Println()
			}
		}

		if n.Limit != nil {
			fmt.Printf("%s  LIMIT: ", indentStr)
			printExpression(n.Limit, 0)
		}

		if n.Offset != nil {
			fmt.Printf("%s  OFFSET: ", indentStr)
			printExpression(n.Offset, 0)
		}

	default:
		fmt.Printf("%s%T\n", indentStr, node)
	}
}

// printExpression 打印表达式
func printExpression(expr parser.Expression, indent int) {
	switch e := expr.(type) {
	case *parser.ColumnRef:
		if e.Table != "" {
			fmt.Printf("列引用: %s.%s\n", e.Table, e.Column)
		} else {
			fmt.Printf("列引用: %s\n", e.Column)
		}

	case *parser.Literal:
		fmt.Printf("字面量: %v (%s)\n", e.Value, getLiteralTypeString(e.Type))

	case *parser.BinaryExpr:
		fmt.Printf("二元表达式 (%s)\n", getBinaryOpString(e.Operator))
		indentStr := strings.Repeat("  ", indent+1)
		fmt.Printf("%s左操作数: ", indentStr)
		printExpression(e.Left, indent+1)
		fmt.Printf("%s右操作数: ", indentStr)
		printExpression(e.Right, indent+1)

	case *parser.UnaryExpr:
		fmt.Printf("一元表达式 (%s)\n", getUnaryOpString(e.Operator))
		indentStr := strings.Repeat("  ", indent+1)
		fmt.Printf("%s操作数: ", indentStr)
		printExpression(e.Operand, indent+1)

	case *parser.FunctionCall:
		fmt.Printf("函数调用: %s\n", e.Name)
		if len(e.Args) > 0 {
			indentStr := strings.Repeat("  ", indent+1)
			fmt.Printf("%s参数:\n", indentStr)
			for i, arg := range e.Args {
				fmt.Printf("%s  [%d] ", indentStr, i)
				printExpression(arg, indent+2)
			}
		}

	// 注意：SubqueryExpr 可能不存在，这里简化处理
	// case *parser.SubqueryExpr:
	//	fmt.Printf("子查询表达式\n")
	//	printASTStructure(e.Query, indent+1)

	default:
		fmt.Printf("%T\n", expr)
	}
}

// 辅助函数
func getLiteralTypeString(t parser.LiteralType) string {
	switch t {
	case parser.StringLiteral:
		return "字符串"
	case parser.IntegerLiteral:
		return "整数"
	case parser.FloatLiteral:
		return "浮点数"
	case parser.BooleanLiteral:
		return "布尔值"
	case parser.NullLiteral:
		return "NULL"
	default:
		return "未知"
	}
}

func getBinaryOpString(op parser.BinaryOp) string {
	switch op {
	case parser.PLUS:
		return "+"
	case parser.MINUS:
		return "-"
	case parser.MULTIPLY:
		return "*"
	case parser.DIVIDE:
		return "/"
	case parser.MODULO:
		return "%"
	case parser.EQ:
		return "="
	case parser.NE:
		return "!="
	case parser.LT:
		return "<"
	case parser.LE:
		return "<="
	case parser.GT:
		return ">"
	case parser.GE:
		return ">="
	case parser.AND:
		return "AND"
	case parser.OR:
		return "OR"
	case parser.CONCAT:
		return "||"
	case parser.IS_NULL:
		return "IS NULL"
	case parser.IS_NOT_NULL:
		return "IS NOT NULL"
	default:
		return "未知操作符"
	}
}

func getUnaryOpString(op parser.UnaryOp) string {
	switch op {
	case parser.NOT:
		return "NOT"
	case parser.UMINUS:
		return "-"
	default:
		return "未知操作符"
	}
}

// demonstrateQueryOptimization 演示查询优化建议
func demonstrateQueryOptimization() {
	// 示例：分析一个复杂查询并提供优化建议
	complexSQL := `
		SELECT u.name, o.total
		FROM users u 
		INNER JOIN orders o ON u.id = o.user_id
		WHERE u.age > 25
		ORDER BY u.name
	`

	fmt.Printf("原始查询:\n%s\n", complexSQL)

	ast, err := parser.Parse(complexSQL)
	if err != nil {
		log.Printf("解析失败: %v", err)
		return
	}

	complexity := analyzeQueryComplexity(ast)
	fmt.Printf("查询复杂度分析:\n")
	fmt.Printf("  - 子查询数量: %d\n", complexity.SubqueryCount)
	fmt.Printf("  - 聚合函数数量: %d\n", complexity.AggregateCount)
	fmt.Printf("  - 复杂度评分: %d\n", complexity.Score)

	fmt.Println("\n优化建议:")
	if complexity.SubqueryCount > 1 {
		fmt.Println("  ⚠️  检测到多个子查询，建议考虑使用JOIN重写以提高性能")
	}
	if complexity.AggregateCount > 0 {
		fmt.Println("  💡 建议在相关列上创建索引以加速聚合计算")
	}

	// 提供优化后的查询示例
	optimizedSQL := `
		SELECT u.name, 
		       COALESCE(stats.order_count, 0) as order_count,
		       COALESCE(stats.total_spent, 0) as total_spent
		FROM users u 
		LEFT JOIN (
		    SELECT user_id, 
		           COUNT(*) as order_count,
		           SUM(total) as total_spent
		    FROM orders 
		    GROUP BY user_id
		) stats ON u.id = stats.user_id
		WHERE u.age > 25
		ORDER BY u.name
	`

	fmt.Printf("\n优化后的查询:\n%s\n", optimizedSQL)
	fmt.Println("优化说明: 将多个子查询合并为一个JOIN操作，减少数据库访问次数")
}